package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import DAO.LernbueroDAO;


import Model.Klasse;

public class KlasseDAO {
	
	private LehrerDAO lehrerDAO = new LehrerDAO();
	private LernbgrpDAO lernbgrpDAO = new LernbgrpDAO();

	
	public Klasse getKlasse(int id)
	{
		Klasse klasse=null;
		Connection conn=ConnectionProvider.getConnection();
		PreparedStatement statement=null;
		ResultSet rs=null;
		String sql="select * from lehrer where lehrerid=?";
        try {
			statement = conn.prepareStatement(sql);
			statement.setInt(1, id);
			rs=statement.executeQuery();
			
			if (rs.next())
			{
				klasse=new Klasse();
				klasse.setId(id);
				klasse.setSchuljahr(rs.getString("schuljahr"));
				klasse.setKbez(rs.getString("kbez"));
				klasse.setLehrer(lehrerDAO.getLehrer(rs.getInt("lehrerID")));
				klasse.setLernbgrp(lernbgrpDAO.getLernbgrp(rs.getInt("lbgruppeID")));
			}
//	lernbuero.setLernbgrp(lernbgrpDAO.getLernbgrp(rs.getInt("lbgruppeID")));

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}   
		finally {
			try {
				rs.close();
				statement.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return klasse;
	}
	
	public void update(Klasse klasse)
	{
		Connection conn=ConnectionProvider.getConnection();
		String sql="update klasse set schuljahr=?, kbez=?, lehrerID=?, lbgruppeID=? where klasseID=?";
        try {
			PreparedStatement statement = conn.prepareStatement(sql);
			statement.setString(1, klasse.getSchuljahr());
			statement.setString(2, klasse.getKbez());
			statement.setInt(3, klasse.getLehrer().getId());
			statement.setInt(4, klasse.getLernbgrp().getId());
			statement.setInt(6, klasse.getId());

			statement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}   
		finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public void anlegen(Klasse klasse)
	{
		Connection conn=ConnectionProvider.getConnection();
		String sql="insert into klasse VALUES (?, ?, ?, ?, ?)";
        try {
			PreparedStatement statement = conn.prepareStatement(sql);
			statement.setInt(1, klasse.getId());
			statement.setString(2, klasse.getSchuljahr());
			statement.setString(3, klasse.getKbez());
			statement.setInt(4, klasse.getLehrer().getId());
			statement.setInt(5, klasse.getLernbgrp().getId());

			statement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}   
		finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public void delete(int id)
	{
		Connection conn=ConnectionProvider.getConnection();
		String sql="delete from klasse where klasseID= ?";
        try {
			PreparedStatement statement = conn.prepareStatement(sql);
			statement.setInt(1, id);
			statement.executeUpdate();
        } catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}   
        finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}
